Skip Headers

Oracle® Call Interface Programmer's Guide, 10g Release 2 (10.2) Part Number B14250-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 1 Introduction and Upgrading

This chapter contains these topics:

- Overview of OCI

- Compatibility and Upgrading

- OCI Instant Client

## Overview of OCI

The Oracle Call Interface (OCI) is an application programming interface (API) that lets you create applications that use function calls to access an Oracle database server and control all phases of SQL statement execution. OCI supports the datatypes, calling conventions, syntax, and semantics of C and C++.

See Also: Oracle C++ Call Interface Programmer's Guide "Related Documents"

OCI provides:

- Improved performance and scalability through the efficient use of system memory and network connectivity

- Consistent interfaces for dynamic session and transaction management in a two-tier client/server or multitier environment

- N-tier authentication

- Comprehensive support for application development using Oracle objects

- Access to external databases

- Applications that support an increasing number of users and requests without additional hardware investments

OCI lets you manipulate data and schemas in an Oracle database using C programming language. It provides a library of standard database access and retrieval functions in the form of a dynamic runtime library (OCI library) that can be linked in an application at runtime.

OCI has many new features that can be categorized into several primary areas:

- Encapsulated or opaque interfaces, whose implementation details are unknown

- Simplified user authentication and password management

- Extensions to improve application performance and scalability

- Consistent interface for transaction management

- OCI extensions to support client-side access to Oracle objects

### Advantages of OCI

OCI provides significant advantages over other methods of accessing an Oracle database:

- More fine-grained control over all aspects of application design

- High degree of control over program execution

- Use of familiar third generation language programming techniques and application development tools, such as browsers and debuggers

- Connection pooling, session pooling, and statement caching that enable building of scalable applications

- Support of dynamic SQL

- Availability on the broadest range of operating systems of all the Oracle programmatic interfaces

- Dynamic binding and defining using callbacks

- Description functionality to expose layers of server metadata

- Asynchronous event notification for registered client applications

- Enhanced array data manipulation language (DML) capability for array inserts, updates, and deletes

- Ability to associate commit requests with executes to reduce round trips

- Optimization of queries using transparent prefetch buffers to reduce round trips

- Thread safety which eliminates the need for mutual exclusive locks (mutexes) on OCI handles

### Building an OCI Application

You compile and link an OCI program in the same way that you compile and link a non-database application. There is no need for a separate preprocessing or precompilation step.

Oracle supports most popular third-party compilers. The details of linking an OCI program vary from system to system. On some operating systems, it may be necessary to include other libraries, in addition to the OCI library, to properly link your OCI programs. See your Oracle system-specific documentation and the installation guide for more information about compiling and linking an OCI application for your operating system.

See Also: Appendix D, "Getting Started with OCI for Windows"

### Parts of OCI

OCI has the following functionality:

- APIs to design a scalable, multithreaded application that can support large numbers of users securely

- SQL access functions, for managing database access, processing SQL statements, and manipulating objects retrieved from an Oracle database server

- Datatype mapping and manipulation functions, for manipulating data attributes of Oracle types

- Data loading functions, for loading data directly into the database without using SQL statements

- External procedure functions, for writing C callbacks from PL/SQL

### Procedural and Non-Procedural Elements

OCI lets you develop scalable, multithreaded applications in a multitier architecture that combines the non-procedural data access power of Structured Query Language (SQL) with the procedural capabilities of C and C++.

- In a non-procedural language program, the set of data to be operated on is specified, but what operations will be performed, or how the operations are to be carried out is not specified. The non-procedural nature of SQL makes it an easy language to learn and to use to perform database transactions. It is also the standard language used to access and manipulate data in modern relational and object-relational database systems.

- In a procedural language program, the execution of most statements depends on previous or subsequent statements and on control structures, such as loops or conditional branches, which are not available in SQL. The procedural nature of these languages makes them more complex than SQL, but it also makes them more flexible and powerful.

The combination of both non-procedural and procedural language elements in an OCI program provides easy access to an Oracle database in a structured programming environment.

OCI supports all SQL data definition, data manipulation, query, and transaction control facilities that are available through an Oracle database server. For example, an OCI program can run a query against an Oracle database. The queries can require the program to supply data to the database using input (bind) variables, as follows:

    SELECT name FROM employees WHERE empno = :empnumber;

In the preceding SQL statement, `:empnumber` is a placeholder for a value that will be supplied by the application.

You can also take advantage of PL/SQL, Oracle's procedural extension to SQL. The applications you develop can be more powerful and flexible than applications written in SQL alone. OCI also provides facilities for accessing and manipulating objects in an Oracle database server.

### Object Support

OCI has facilities for working with object types and objects. An object type is a user-defined data structure representing an abstraction of a real-world entity. For example, the database might contain a definition of a `person` object. That object might have attributes—`first_name`, `last_name`, and `age`—which represent a person's identifying characteristics.

The object type definition serves as the basis for creating objects, which represent instances of the object type. Using the object type as a structural definition, a `person` object could be created with the attribute values 'John', 'Bonivento', and '30'. Object types may also contain methods—programmatic functions that represent the behavior of that object type.

See Also: Oracle Database Concepts Oracle Database Application Developer's Guide - Object-Relational Features.

OCI includes functions that extend the capabilities of OCI to handle objects in an Oracle database server. Specifically, the following capabilities have been added to OCI:

- Executing SQL statements that manipulate object data and schema information

- Passing of object references and instances as input variables in SQL statements

- Declaring object references and instances as variables to receive the output of SQL statements

- Fetching object references and instances from a database

- Describing the properties of SQL statements that return object instances and references

- Describing PL/SQL procedures or functions with object parameters or results

- Extension of commit and rollback calls in order to synchronize object and relational functionality

Additional OCI calls are provided to support manipulation of objects after they have been accessed by SQL statements. For a more detailed description of enhancements and new features, refer to "Encapsulated Interfaces".

### SQL Statements

One of the main tasks of an OCI application is to process SQL statements. Different types of SQL statements require different processing steps in your program. It is important to take this into account when coding your OCI application. Oracle recognizes several types of SQL statements:

- Data Definition Language (DDL)

- Control Statements

  - Transaction Control

  - Session Control

  - System Control

- Data Manipulation Language (DML)

- Queries

  Note: Queries are often classified as DML statements, but OCI applications process queries differently, so they are considered separately here.

- PL/SQL

- Embedded SQL

  See Also: Chapter 4, "Using SQL Statements in OCI"

#### Data Definition Language

Data definition language (DDL) statements manage schema objects in the database. DDL statements create new tables, drop old tables, and establish other schema objects. They also control access to schema objects.

The following is an example of creating and specifying access to a table:

    CREATE TABLE employees
        (name       VARCHAR2(20),
         ssn        VARCHAR2(12),
         empno      NUMBER(6),
         mgr        NUMBER(6),
         salary     NUMBER(6));

    GRANT UPDATE, INSERT, DELETE ON employees TO donna;
    REVOKE UPDATE ON employees FROM jamie;

DDL statements also allow you to work with objects in the Oracle database server, as in the following series of statements which creates an object table:

    CREATE TYPE person_t AS OBJECT (
        name     VARCHAR2(30),
        ssn      VARCHAR2(12),
        address  VARCHAR2(50));

    CREATE TABLE person_tab OF person_t;

#### Control Statements

OCI applications treat transaction control, session control, and system control statements like DML statements.

See Also: Oracle Database SQL Reference for information about these types of statements

#### Data Manipulation Language

Data manipulation language (DML) statements can change data in the database tables. For example, DML statements are used to:

- Insert new rows into a table

- Update column values in existing rows

- Delete rows from a table

- Lock a table in the database

- Explain the execution plan for a SQL statement

- Require an application to supply data to the database using input (bind) variables

  See Also: "Binding Placeholders in OCI" for more information about input bind variables

DML statements also allow you to work with objects in the Oracle database server, as in the following example, which inserts an instance of type `person_t` into the object table `person_tab`:

    INSERT INTO person_tab
        VALUES (person_t('Steve May','123-45-6789','146 Winfield Street'));

#### Queries

Queries are statements that retrieve data from a database. A query can return zero, one, or many rows of data. All queries begin with the SQL keyword `SELECT`, as in the following example:

    SELECT dname FROM dept
         WHERE deptno = 42;

Queries access data in tables, and they are often classified with DML statements. However, OCI applications process queries differently, so they are considered separately in this guide.

Queries can require the program to supply data to the database using input (bind) variables, as in the following example:

    SELECT name
        FROM employees
        WHERE empno = :empnumber;

In the preceding SQL statement, `:empnumber` is a placeholder for a value that will be supplied by the application.

- When processing a query, an OCI application also needs to define output variables to receive the returned results. In the preceding statement, you would need to define an output variable to receive any `name` values returned from the query.

  See Also: "Overview of Binding in OCI" for more information about input bind variables. See the section "Overview of Defining in OCI" for information about defining output variables. Chapter 4, "Using SQL Statements in OCI", for detailed information about how SQL statements are processed in an OCI program.

#### PL/SQL

PL/SQL is Oracle's procedural extension to the SQL language. PL/SQL processes tasks that are more complicated than simple queries and SQL data manipulation language statements. PL/SQL allows a number of constructs to be grouped into a single block and executed as a unit. Among these are:

- One or more SQL statements

- Variable declarations

- Assignment statements

- Procedural control statements (IF...THEN...ELSE statements and loops)

- Exception handling

You can use PL/SQL blocks in your OCI program to:

- Call Oracle stored procedures and stored functions

- Combine procedural control statements with several SQL statements, so that they are executed as a single unit

- Access special PL/SQL features such as records, tables, cursor FOR loops, and exception handling

- Use cursor variables

- Access and manipulate objects in an Oracle database server

The following PL/SQL example issues a SQL statement to retrieve values from a table of employees, given a particular employee number. This example also demonstrates the use of placeholders in PL/SQL statements.

    BEGIN
        SELECT ename, sal, comm INTO :emp_name, :salary, :commission
        FROM emp
        WHERE empno = :emp_number;
    END;

Note that the placeholders in this statement are not PL/SQL variables. They represent input values passed to Oracle when the statement is processed. These placeholders need to be bound to C language variables in your program.

See Also: Oracle Database PL/SQL User's Guide and Reference for information about coding PL/SQL blocks. "Binding Placeholders in PL/SQL" for information about working with placeholders in PL/SQL.

#### Embedded SQL

OCI processes SQL statements as text strings that an application passes to Oracle on execution. The Oracle precompilers (Pro*C/C++, Pro*COBOL, Pro*FORTRAN) allow you to embed SQL statements directly into your application code. A separate precompilation step is then necessary to generate an executable application.

It is possible to mix OCI calls and embedded SQL in a precompiler program.

See Also: Pro*C/C++ Programmer's Guide

#### Special OCI/SQL Terms

This guide uses special terms to refer to the different parts of a SQL statement. For example, a SQL statement such as

    SELECT customer, address
    FROM customers
    WHERE bus_type = 'SOFTWARE'
    AND sales_volume = :sales;

contains the following parts:

- A SQL command - `SELECT`

- Two select-list items - `customer` and `address`

- A table name in the `FROM` clause - `customers`

- Two column names in the `WHERE` clause - `bus_type` and `sales_volume`

- A literal input value in the `WHERE` clause - '`SOFTWARE`'

- A placeholder for an input variable in the `WHERE` clause - `:sales`

When you develop your OCI application, you call routines that specify to the Oracle database server the address (location) of input and output variables of your program. In this guide, specifying the address of a placeholder variable for data input is called a bind operation. Specifying the address of a variable to receive select-list items is called a define operation.

For PL/SQL, both input and output specifications are called bind operations. These terms and operations are described in Chapter 4, "Using SQL Statements in OCI".

### Encapsulated Interfaces

All the data structures that are used by OCI calls are encapsulated in the form of opaque interfaces that are called handles. A handle is an opaque pointer to a storage area allocated by the OCI library that stores context information, connection information, error information, or bind information about a SQL or PL/SQL statement. A client allocates a certain types of handles, populates one or more of those handles through well-defined interfaces, and sends requests to the server using those handles. In turn, applications can access the specific information contained in the handle by using accessor functions.

The OCI library manages a hierarchy of handles. Encapsulating the OCI interfaces by means of these handles has several benefits to the application developer, including:

- Reduction in the amount of server side state information that needs to be retained, thereby reducing server-side memory usage

- Improvement of productivity by eliminating the need for global variables, making error reporting easier, and providing consistency in the way OCI variables are accessed and used

- Encapsulation of OCI structures in the form of handles makes them opaque, allowing changes to be made to the underlying structure without affecting applications

### Simplified User Authentication and Password Management

OCI provides application developers with simplified user authentication and password management in several ways:

- Allows a single OCI application to authenticate and maintain multiple users

- Allows the application to update a user's password, which is particularly helpful if an expired password message is returned by an authentication attempt

OCI supports two types of login sessions:

- A simplified login function for sessions by which a single user connects to the database using a login name and password

- A mechanism by which a single OCI application authenticates and maintains multiple sessions by separating the login session, which is the session created when a user logs into an Oracle database, from the user sessions, which are all other sessions created by a user

### Extensions to Improve Application Performance and Scalability

OCI has several enhancements to improve application performance and scalability. Application performance has been improved by reducing the number of client to server round trips required and scalability improvements have been made by reducing the amount of state information that needs to be retained on the server side. Some of these features include:

- Increased client-side processing, and reduced server-side requirements on queries

- Implicit prefetching of `SELECT` statement result sets to eliminate the describe round trip, reduce round trips, and reduce memory usage

- Elimination of open and closed cursor round trips

- Improved support for multithreaded environments

- Session multiplexing over connections

- Consistent support for a variety of configurations, including standard two-tier client/server configurations, server-to-server transaction coordination, and three-tier TP-monitor configurations

- Consistent support for local and global transactions including support for the XA interface's TM_JOIN operation

- Improved scalability by providing the ability to concentrate connections, processes, and sessions across users on connections and eliminating the need for separate sessions to be created for each branch of a global transaction

- Allowing applications to authenticate multiple users and allow transactions to be started on their behalf

### OCI Object Support

OCI provides a comprehensive application programming interface for programmers seeking to use the Oracle server's object capabilities. These features can be divided into five major categories:

- Client-Side Object Caching

- Associative and navigational interfaces to access and manipulate objects

- Runtime environment for objects

- Type management functions to access information about object types in an Oracle database

- Type mapping and manipulation functions for controlling data attributes of Oracle types

- Object Type Translator utility, for mapping internal Oracle schema information to client-side language bind variables

### Client-Side Object Cache

The object cache is a client-side memory buffer that provides lookup and memory management support for objects. It stores and tracks object instances that have been fetched by an OCI application from the server to the client side. The object cache is created when the OCI environment is initialized. Multiple applications running against the same server will each have their own object cache. The cache tracks the objects which are currently in memory, maintains references to objects, manages automatic object swapping and tracks the meta-attributes or type information about objects. The object cache provides the following to OCI applications:

- Improved application performance by reducing the number of client/server round trips required to fetch and operate on objects

- Enhanced scalability by supporting object swapping from the client-side cache

- Improved concurrency by supporting object-level locking

### Associative and Navigational Interfaces

Applications using OCI can access objects in the Oracle server through several types of interfaces:

- Using SQL `SELECT`, `INSERT`, and `UPDATE` statements

- Using a C-style pointer chasing scheme to access objects in the client-side cache by traversing the corresponding smart pointers or REFs

OCI provides a set of functions with extensions to support object manipulation using SQL `SELECT`, `INSERT`, and `UPDATE` statements. To access Oracle objects these SQL statements use a consistent set of steps as if they were accessing relational tables. OCI provides the following sets of functions required to access objects:

- Binding and defining object type instances and references as input and output variables of SQL statements

- Executing SQL statements that contain object type instances and references

- Fetching object type instances and references

- Describing select-list items of an Oracle object type

OCI also provides a set of functions using a C-style pointer chasing scheme to access objects once they have been fetched into the client-side cache by traversing the corresponding smart pointers or REFs. This navigational interface provides functions for:

- Instantiating a copy of a referenceable persistent object, that is, of a persistent object with object ID in the client-side cache by pinning its smart pointer or `REF`

- Traversing a sequence of objects that are connected to each other by traversing the `REF`s that point from one to the other

- Dynamically getting and setting values of an object's attributes

### OCI Runtime Environment for Objects

OCI provides functions for objects that manages how Oracle objects are used on the client-side. These functions provide for:

- Connecting to an Oracle server in order to access its object functionality, including initializing a session, logging on to a database server, and registering a connection

- Setting up the client-side object cache and tuning its parameters

- Getting errors and warning messages

- Controlling transactions that access objects in the server

- Associatively accessing objects through SQL

- Describing a PL/SQL procedure or function whose parameters or result are Oracle types

### Type Management, Mapping and Manipulation Functions

OCI provides two sets of functions to work with Oracle objects:

- Type Mapping functions allow applications to map attributes of an Oracle schema represented in the server as internal Oracle datatypes to their corresponding host language types.

- Type Manipulation functions allow host language applications to manipulate individual attributes of an Oracle schema such as setting and getting their values and flushing their values to the server.

Additionally, the `OCIDescribeAny()` function provides information about objects stored in the database.

### Object Type Translator

The Object Type Translator (OTT) utility translates schema information about Oracle object types into client-side language bindings of host language variables, such as structures. The OTT takes as input an `intype` file which contains metadata information about Oracle schema objects. It generates an `outtype` file and the necessary header and implementation files that must be included in a C application that runs against the object schema. Both OCI applications and Pro*C/C++ precompiler applications may include code generated by the OTT. The OTT has many benefits including:

- Improves application developer productivity: OTT eliminates the need for you to code the host language variables that correspond to schema objects.

- Maintains SQL as the data-definition language of choice: By providing the ability to automatically map Oracle schema objects that are created using SQL to host language variables, OTT facilitates the use of SQL as the data-definition language of choice. This in turn allows Oracle to support a consistent model of data.

- Facilitates schema evolution of object types: OTT regenerates included header files when the schema is changed, allowing Oracle applications to support schema evolution.

OTT is typically invoked from the command line by specifying the `intype` file, the `outtype` file and the specific database connection. With Oracle, OTT can only generate C structures which can either be used with OCI programs or with the Pro*C/C++ precompiler programs

### OCI Support for Oracle Streams Advanced Queuing

OCI provides an interface to Oracle's Streams Advanced Queuing (Streams AQ) feature. Streams AQ provides message queuing as an integrated part of the Oracle server. Streams AQ provides this functionality by integrating the queuing system with the database, thereby creating a message-enabled database. By providing an integrated solution Streams AQ frees you to devote your efforts to your specific business logic rather than having to construct a messaging infrastructure.

See Also: "OCI and Streams Advanced Queuing".

### XA Library Support

OCI supports the Oracle XA library. The `xa.h` header file is in the same location as all the other OCI header files. For Linux or UNIX, the path is `$ORACLE_HOME/rdbms/public`. Users of the `demo_rdbms.mk` file on Linux or UNIX are not affected because the directory `$ORACLE_HOME/rdms/public` is already in the file.

For Windows, the path is `ORACLE_BASE\ORACLE_HOME`\`oci\include`.

See Also: "The Oracle XA Library" for more information about Windows and XA applications Oracle Database Application Developer's Guide - Fundamentals, chapter "Developing Applications with Oracle XA".

## Compatibility and Upgrading

The following sections discuss issues concerning compatibility between different versions of OCI client and server, changes in the OCI library routines, and upgrading an application from the release 7.x OCI to this release of OCI.

### Simplified Upgrading of Existing OCI Release 7 Applications

OCI has been significantly improved with many features. Applications written to work with OCI release 7 have a smooth migration path to this OCI release because of the interoperability of OCI release 7 clients with this release of the server, and of clients of this release with an Oracle database version 7 server.

Specifically:

- Applications that use the OCI release 7.3 API will work unchanged against this release of the server. They do need to be linked with the current client library.

- OCI release 7 and the OCI calls of this release can be mixed in the same application and in the same transaction provided they are not mixed within the same statement execution.

As a result, when migrating an existing OCI version 7 application you have the following two alternatives:

- Upgrade to the current OCI client but do not modify the application: If you choose to upgrade from an Oracle release 7 OCI client to the current release OCI client, you need only link the new version of the OCI library and need not recompile your application. The re-linked Oracle release 7 OCI applications work unchanged against a current server.

- Upgrade to the current OCI client and modify the application: To use the performance and scalability benefits provided by the new OCI, however, you will need to modify your existing applications to use the new OCI programming paradigm, re-link them with the new OCI library, and run them against the current release of the server.

If you need to use any of the object capabilities of the current server release, you will need to upgrade your client to this release of OCI.

### Statically-Linked and Dynamically-Linked Applications

Here are the rules for re-linking for a new release.

- Statically-linked applications:

  Statically-linked applications need to be re-linked for both major and minor releases, because the linked Oracle client-side library code may be incompatible with the error messages in the upgraded `ORACLE_HOME`. For example, if an error message was updated with additional parameters then it will not be compatible with the statically-linked code.

- Dynamically-linked applications:

  Dynamically-linked applications need to be re-linked for major releases only. OCI applications that are dynamically linked have a hard reference to the `libclntsh.so.n`, where `n` is the major release number.

  See Also: Oracle Database Upgrade Guide for information about compatibility and upgrading The server versions supported currently are found on Oracle iSupport in note 207303.1. See the URL `http://metalink.oracle.com/`

### Obsolete OCI Routines

Release 8.0 of the OCI introduced an entirely new set of functions which were not available in release 7.3. Oracle continues to support these release 7.3 functions. Many of the earlier 7.x calls are available, but Oracle strongly recommends that new applications use the new calls to improve performance and provide increased functionality.

Table 1-1, "Obsolescent OCI Functions" lists the 7.x OCI calls with their later equivalents. For more information about the OCI calls, see the function descriptions in this guide. For more information about the 7.x calls, see the Programmer's Guide to the Oracle Call Interface, Release 7.3. These 7.x calls are obsoleted, meaning that OCI has replaced them with newer calls. While the obsoleted calls are supported at this time, they may not be supported in all future versions of OCI.

Note: In many cases the new OCI routines do not map directly onto the 7.x routines, so it may not be possible to simply replace one function call and parameter list with another. Additional program logic may be required before or after the new call is made. See the remaining chapters of this guide for more information.

Table 1-1 Obsolescent OCI Functions

7.x OCI Routine | Equivalent or Similar Later OCI Routine
--- | ---
`obindps(), obndra(), obndrn(), obndrv()` | `OCIBindByName()`, `OCIBindByPos()` (Note: additional bind calls may be necessary for some datatypes)
`obreak()` | `OCIBreak()`
`ocan()` | none
`oclose()` | Note: cursors are not used in release 8.x or higher
`ocof(), ocon()` | `OCIStmtExecute()` with `OCI_COMMIT_ON_SUCCESS` mode
`ocom()` | `OCITransCommit()`
`odefin(), odefinps()` | `OCIDefineByPos()` (Note: additional define calls may be necessary for some datatypes)
`odescr()` | Note: schema objects are described with OCIDescribeAny(). A describe, as used in release 7.x, will most often be done by calling OCIAttrGet() on the statement handle after SQL statement execution.
`odessp()` | `OCIDescribeAny()`
`oerhms()` | `OCIErrorGet()`
`oexec(), oexn()` | `OCIStmtExecute()`
`oexfet()` | `OCIStmtExecute()`, `OCIStmtFetch()` (Note: result set rows can be implicitly prefetched)
`ofen()`, `ofetch()` | `OCIStmtFetch()`
`oflng()` | none
`ogetpi()` | `OCIStmtGetPieceInfo()`
`olog()` | `OCILogon()`
`ologof()` | `OCILogoff()`
`onbclr(), onbset(), onbtst()` | Note: nonblocking mode can be set or checked by calling `OCIAttrSet()` or `OCIAttrGet()` on the server context handle or service context handle
`oopen()` | Note: cursors are not used in release 8.x or later
`oopt()` | none
`oparse()` | `OCIStmtPrepare()`; however, it is all local
`opinit()` | `OCIEnvCreate()`
`orol()` | `OCITransRollback()`
`osetpi()` | `OCIStmtSetPieceInfo()`
`sqlld2()` | `SQLSvcCtxGet or SQLEnvGet`
`sqllda()` | `SQLSvcCtxGet or SQLEnvGet`
`odsc()` | Note: see odescr() preceding
`oermsg()` | `OCIErrorGet()`
`olon()` | `OCILogon()`
`orlon()` | `OCILogon()`
`oname()` | Note: see `odescr()` preceding
`osql3()` | Note: see `oparse()` preceding

### OCI Routines Not Supported

Some OCI routines that were available in previous versions of OCI are not supported in later releases. They are listed in Table 1-2, "OCI Functions Not Supported":

Table 1-2 OCI Functions Not Supported

OCI Routine | Equivalent or Similar Later OCI Routine
--- | ---
`obind()` | `OCIBindByName()`, `OCIBindByPos()` (Note: additional bind calls may be necessary for some datatypes)
`obindn()` | `OCIBindByName()`, `OCIBindByPos()` (Note: additional bind calls may be necessary for some datatypes)
`odfinn()` | `OCIDefineByPos()` (Note: additional define calls may be necessary for some datatypes)
`odsrbn()` | Note: see `odescr()` in Table 1-1
`ologon()` | `OCILogon()`
`osql()` | Note: see `oparse()` Table 1-1

### Compatibility Between Different Releases of OCI and Servers

This section addresses compatibility between different releases of OCI and Oracle server.

Existing 7.x applications with no new post-release 7.x calls have to be re-linked with the new client-side library.

The application will not be able to use the object features of Oracle8i or later, and will not get any of the performance or scalability benefits provided by those OCI releases.

### Upgrading OCI

Programmers who wish to incorporate release post-release 7.x functionality into existing OCI applications have two options:

- Completely rewrite the application to use only new OCI calls (recommended).

- Incorporate new OCI post-release 7.x calls into the application, while still using 7.x calls for some operations.

This manual should provide the information necessary to rewrite an existing application to use only new OCI calls.

#### Adding Post-release 7.x OCI Calls to 7.x Applications

The following guidelines apply to programmers who want to incorporate new Oracle datatypes and features by using new OCI calls, while keeping 7.x calls for some operations:

- Change the existing logon to use `OCILogon()` instead of `olog()` (or other logon call). The service context handle can be used with new OCI calls or can be converted into an `Lda_Def` to be used with 7.x OCI calls.

  See Also: See the description of OCIServerAttach() and the description of OCISessionBegin() for information about the logon calls necessary for applications which are maintaining multiple sessions.

- After the server context handle has been initialized, it can be used with OCI post-release 7.x calls.

- To use release 7 OCI calls, convert the server context handle to an `Lda_Def` using `OCISvcCtxToLda()`, and pass the resulting `Lda_Def` to the 7.x calls.

Note: If there are multiple service contexts that share the same server handle, only one can be in Oracle version 7 mode at any one time.

- To begin using post-release 7.x OCI calls again, the application must convert the `Lda_Def` back to a server context handle using `OCILdaToSvcCtx()`.

- The application may toggle between the `Lda_Def` and server context as often as necessary in the application.

This approach allows an application to use a single connection, but two different APIs, to accomplish different tasks.

You can mix OCI 7.x and post-release 7.x calls within a transaction, but not within a statement. This lets you execute one SQL or PL/SQL statement with OCI 7.x calls and the next SQL or PL/SQL statement within that transaction with post-release 7.x OCI calls.

Caution: You cannot open a cursor, parse with OCI 7.x calls and then execute the statement with post-release 7.x calls.

## OCI Instant Client

The Instant Client feature makes it extremely easy to deploy OCI, OCCI, ODBC, and JDBC-OCI based customer applications by eliminating the need for an `ORACLE_HOME`. The storage space requirement of an OCI application running in Instant Client mode is significantly reduced compared to the same application running in a full client side installation. The Instant Client shared libraries only occupy about one-fourth the disk space of a full client installation.

Table 1-3 shows the Oracle client side files required to deploy an OCI application:

Table 1-3 OCI Instant Client Shared Libraries

Linux and UNIX | Description for Linux and UNIX | Windows | Description for Windows
--- | --- | --- | ---
`libclntsh.so.10.1` | Client Code Library | `oci.dll` | Forwarding functions that applications link with
`libociei.so` | OCI Instant Client Data Shared Library | `oraociei10.dll` | Data and code
`libnnz10.so` | Security Library | `orannzsbb10.dll` | Security Library

Oracle Database 10g Release 2 library names are used in the table.

To use the Microsoft ODBC and OLEDB driver, `ociw32.dll` must also be copied from ORACLE_HOME\bin.

### Benefits of Instant Client

Why use Instant Client? Here are the reasons:

- Installation involves copying a small number of files.

- The Oracle client-side number of required files and the total disk storage are significantly reduced.

- There is no loss of functionality or performance for applications deployed in Instant Client mode.

- It is simple for independent software vendors to package applications.

### OCI Instant Client Installation Process

The Instant Client libraries can also be installed by choosing the Instant Client option from the Oracle Universal Installer. The Instant Client libraries can also be downloaded from the Oracle Technology Network (`http://www.oracle.com/technology/index.html`) Web site. The installation process is as simple as:

1. Downloading and installing the Instant Client shared libraries to a directory such as `instantclient_10_2`.

2. Setting the operating system shared library path environment variable to the directory from step 1. For example, on Linux or UNIX, set the LD_LIBRARY_PATH to `instantclient_10_2`. On Windows, set PATH to locate the `instantclient_10_2` directory.

After completing the preceding two steps you are ready to run the OCI application.

The OCI application operates in Instant Client mode when the OCI shared libraries are accessible through the operating system Library Path variable. In this mode, there is no dependency on `ORACLE_HOME` and none of the other code and data files provided in `ORACLE_HOME` are needed by OCI (except for the `tnsnames.ora` file described later).

Instant Client can be installed from the Oracle Universal Installer by selecting the Instant Client option. The installation should be done into an empty directory. As with the OTN install, you must set the `LD_LIBRARY_PATH` to the instant client directory to operate in instant client mode.

If you have done a complete client installation (by choosing the `Admin` option) the Instant Client shared libraries are also installed. The locations of the Instant Client shared libraries in a full client installation are:

On Linux or UNIX:

`libociei.so` library is in `$ORACLE_HOME/instantclient`

`libclntsh.so.10.1` and `libnnz10.so` are in `$ORACLE_HOME/lib`

On Windows:

`oraociei10.dll` library is in `ORACLE_HOME\instantclient`

`oci.dll`, `ociw32.dll`, and `orannzsbb10.dll` are in `ORACLE_HOME\bin`

By copying the preceding libraries to a different directory and setting the operating system shared library path to locate this directory you can enable running the OCI application in Instant Client mode.

Note: All the libraries must be copied from the same `ORACLE_HOME` and must be placed in the same directory. There should be only one set of Oracle libraries on the operating system Library Path variable. That is, if you have multiple directories containing Instant Client libraries, then only one such directory should be on the operating system Library Path. Similarly, if an `ORACLE_HOME`-based installation is done on the same machine, then you should not have `ORACLE_HOME/lib` and Instant Client directory on the operating system Library Path simultaneously regardless of the order in which they appear on the Library Path. That is, only one of `ORACLE_HOME/lib` directory (for non-Instant Client operation) or Instant Client directory (for Instant Client operation) should be on the operating system Library Path variable.

To enable other capabilities such as OCCI and JDBC-OCI, a few other files need to be copied over as well. In particular, for the JDBC OCI driver, in addition to the three OCI shared libraries, you must also download OCI JDBC Library (for example `libocijdbc10.so` on Linux or UNIX and `oraocijdbc10.dll` on Windows) and `ojdbcXY.jar` (where XY is the version number, for example, `ojdbc14.jar`). All libraries must be in the Instant Client directory and `ojdbcXY.jar` must be able to be loaded from CLASSPATH.

Note: On hybrid platforms, such as Sparc64, if the JDBC OCI driver needs to be operated in the Instant Client mode, the `libociei.so` library must be copied from the `ORACLE_HOME/instantclient32` directory. All other Sparc64 libraries needed for the JDBC OCI Instant Client must be copied from the `ORACLE_HOME/lib32` directory.

For OCCI, the OCCI Library (`libocci.so.10.1` on Linux or UNIX and `oraocci10.dll` on Windows) must also be installed in the Instant Client directory.

### When to Use Instant Client

Instant Client is a deployment feature and should be used for running production applications. In general, all OCI functionality is available to an application being run in the Instant Client mode, except that the Instant Client mode is for client-side operation only. Therefore, server-side external procedures cannot operate in the Instant Client mode.

For development you can also use the Instant Client SDK.

See Also: "SDK for Instant Client"

### Patching Instant Client Shared Libraries on Linux or UNIX

Because Instant Client is a deployment feature, the emphasis has been on reducing the number and size of files (client footprint) required to run an OCI application. Hence all files needed to patch Instant Client shared libraries are not available in an Instant Client deployment. An `ORACLE_HOME` based full client installation is needed to patch the Instant Client shared libraries. The `opatch` utility will take care of patching the Instant Client shared libraries.

After applying the patch in an `ORACLE_HOME` environment, copy the files listed in Table 1-3, "OCI Instant Client Shared Libraries" to the instant client directory as described in "OCI Instant Client Installation Process".

Instead of copying individual files, you can generate Instant Client zip files for OCI/OCCI, JDBC, and SQL*Plus as described in "Regeneration of Data Shared Library and Zip Files". Then, instead of copying individual files as described above, you can instead copy the zip files to the target machine and unzip them as described in"OCI Instant Client Installation Process" .

The `opatch` utility stores the patching information of the `ORACLE_HOME` installation in `libclntsh.so` . This information can be retrieved by the following command:

    genezi -v

Note that if the Instant Client deployment machine does not have the `genezi` utility, then it must be copied from the ORACLE_HOME/bin directory of the ORACLE_HOME machine.

Note: The `opatch` utility is not available on Windows.

### Regeneration of Data Shared Library and Zip Files

The OCI Instant Client Data Shared Library (`libociei.so`) can be regenerated by performing the following steps in an Administrator Install of `ORACLE_HOME`:

    mkdir -p $ORACLE_HOME/rdbms/install/instantclient/light
    cd $ORACLE_HOME/rdbms/lib
    make -f ins_rdbms.mk ilibociei

A new version of `libociei.so` based on the current files in the `ORACLE_HOME` is then placed in the `ORACLE_HOME/rdbms/install/instantclient` directory.

Note that the location of the regenerated Data Shared Library (`libociei.so`) is different from the original Data Shared Library (`libociei.so`) which is located in the `ORACLE_HOME/instantclient` directory.

The above steps also generate Instant Client zip files for OCI/OCCI, JDBC, and SQL*Plus.

Regeneration of data shared library and zip file is not available on Windows platforms.

### Database Connection Strings for OCI Instant Client

The OCI Instant Client can make remote database connections in all the ways that ordinary SQL clients can. However because the Instant Client does not have the `ORACLE_HOME` environment and directory structure some of the database naming methods will require additional configuration steps.

All Oracle net naming methods that do not require use of `ORACLE_HOME` or `TNS_ADMIN` (to locate configuration files such as `tnsnames.ora` or `sqlnet.ora`) work in the Instant Client mode. In particular, the `connect_identifier` in the `OCIServerAttach()` call can be specified in the following formats:

- A SQL Connect URL string of the form:

      [//]host[:port][/service name]

  such as:

      //dlsun242:5521/bjava21

- As an Oracle Net connect descriptor. For example:

      "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=dlsun242) (PORT=5521))
      (CONNECT_DATA=(SERVICE_NAME=bjava21)))"

- A Connection Name that is resolved through Directory Naming where the site is configured for LDAP server discovery.

For naming methods such as `tnsnames` and directory naming to work the `TNS_ADMIN` environment variable must be set.

See Also: Oracle Database Net Services Administrator's Guide chapter on "Configuring Naming Methods" for more about connect descriptors

If the `TNS_ADMIN` environment variable is not set, and `TNSNAMES` entries such as `inst1`, and so on, are used, then the `ORACLE_HOME` variable must be set, and the configuration files are expected to be in the `$ORACLE_HOME/network/admin` directory.

Note that the `ORACLE_HOME` variable in this case is only used for locating Oracle Net configuration files, and no other component of Client Code Library (OCI, NLS, and so on) uses the value of `ORACLE_HOME`.

If a `NULL` string, "", is used as the connection string in the `OCIServerAttach()` call, then the `TWO_TASK` environment variable can be set to the `connect_identifier`. On Windows platform, the `LOCAL` environment variable is used instead of `TWO_TASK`.

Similarly for OCI command line applications such as SQL*Plus, the `TWO_TASK` (or `LOCAL` on Windows) environment variable can be set to the connect_identifier. Its value can be anything that would have gone to the right of the '@' on a typical connect string.

#### Examples of Instant Client Connect Identifiers

If you are using SQL*Plus in Instant Client mode, then you can specify the connect identifier in the following ways:

If the `listener.ora` file on the Oracle database server contains the following:

    LISTENER = (ADDRESS_LIST=
     (ADDRESS=(PROTOCOL=tcp)(HOST=server6)(PORT=1573))
    )

    SID_LIST_LISTENER = (SID_LIST=
     (SID_DESC=(SID_NAME=rdbms3)(GLOBAL_DBNAME=rdbms3.server6.us.alchemy.com)
    (ORACLE_HOME=/home/dba/rdbms3/oracle))
    )

The SQL*Plus connect identifier is:

    "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server6)(PORT=1573))(CONNECT_DATA=
    (SERVICE_NAME=rdbms3.server6.us.alchemy.com)))"

or

    "//server6:1573/rdbms3.server6.us.alchemy.com"

Alternatively, you can set the `TWO_TASK` environment variable to any of the previous connect identifiers and connect without specifying the connect identifier. For example:

    setenv TWO_TASK "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server6)(PORT=1573))
    (CONNECT_DATA=(SERVICE_NAME=rdbms3.server6.us.alchemy.com)))"

or

    setenv TWO_TASK //server6:1573/rdbms3.server6.us.alchemy.com

and invoke SQL*Plus with an empty connect identifier:

    sqlplus user/password

The connect descriptor can also be stored in the `tnsnames.ora` file. For example, if the `tnsnames.ora` file contains the following connect descriptor:

    conn_str = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server6)(PORT=1573))(CONNECT_DATA=
    (SERVICE_NAME=rdbms3.server6.us.alchemy.com)))

and the `tnsnames.ora` is located in the `/home/webuser/instantclient` directory, then you can set the variable `TNS_ADMIN` (or `LOCAL` on Windows) as:

    setenv TNS_ADMIN /home/webuser/instantclient

and then use the connect identifier `conn_str` for invoking SQL*Plus, or for your OCI connection.

Note: `TNS_ADMIN` specifies the directory where the `tnsnames.ora` file is located and `TNS_ADMIN` is not the full path of the `tnsnames.ora` file.

If the above `tnsnames.ora` file is located in an `ORACLE_HOME`-based install in the /`network/server6/home/dba/oracle/network/admin` directory, then the `ORACLE_HOME` environment variable can be set as:

    setenv ORACLE_HOME /network/server6/home/dba/oracle

and SQL*Plus can be invoked as previously, with the identifier `conn_str`.

Finally, if `tnsnames.ora` can be located by `TNS_ADMIN` or `ORACLE_HOME`, then `TWO_TASK` can be set to:

    setenv TWO_TASK conn_str

and SQL*Plus can be invoked without a connect identifier.

### Environment Variables for OCI Instant Client

The `ORACLE_HOME` environment variable no longer determines the location of NLS, CORE, and error message files. An OCI-only application should not require `ORACLE_HOME` to be set. However, if it is set, it does not have an impact on OCI's operation. OCI will always obtain its data from the Data Shared Library. If the Data Shared Library is not available, only then is `ORACLE_HOME` used and a full client installation is assumed. Even though `ORACLE_HOME` is not required to be set, if it is set, then it must be set to a valid operating system path name that identifies a directory.

If Dynamic User callback libraries are to be loaded, then as this guide specifies, the callback package has to reside in `ORACLE_HOME/lib` (`ORACLE_HOME\bin` on Windows). Therefore, `ORACLE_HOME` should be set in this case.

Environment variables `ORA_NLS10` and `ORA_NLS_PROFILE33` are ignored in the Instant Client mode.

In the Instant Client mode, if the `ORA_TZFILE` variable is not set, then the smaller, default, `timezone.dat` file from the Data Shared Library is used. If the larger `timezlrg.dat` file is to be used from the Data Shared Library, then set the `ORA_TZFILE` environment variable to the name of the file without any absolute or relative path names. That is, on Linux or UNIX:

    setenv ORA_TZFILE timezlrg.dat

On Windows:

    set ORA_TZFILE=timezlrg.dat

If OCI is not operating in the Instant Client mode (because the Data Shared Library is not available), then `ORA_TZFILE` variable, if set, names a complete path name as it does in previous Oracle releases.

If `TNSNAMES` entries are used, then, as mentioned earlier, `TNS_ADMIN` directory must contain the `TNSNAMES` configuration files, and if `TNS_ADMIN` is not set, then the `ORACLE_HOME/network/admin` directory must contain Oracle Net Services configuration files.

### Instant Client Light (English)

The Instant Client Light (English) version of Instant Client further reduces the disk space requirements of the client installation by about another 63 MB. For example, the Instant Client Light data shared library, `libociicus.so` on Unix operating systems, occupies only 4 MB, as opposed to 70 MB for `libociei.so`.

This Instant Client Light version is geared toward applications that require English-only error messages and use either US7ASCII, WE8DEC, or one of the Unicode characters. There is no restriction on the `TERRITORY` field of the `NLS_LANG` setting, so the Instant Client Light will operate with any territory setting. Therefore, an application using US7ASCII, WE8DEC, or Unicode can significantly reduce its footprint if it operates in the Instant Client Light environment.

#### Globalization Settings

Instant Client Light supports the following character sets:

Single-byte

- US7ASCII

- WE8DEC

- WE8MSWIN1252

- WE8ISO8859P1

Unicode

- UTF8

- AL16UTF16

- AL32UTF8

Instant Client Light will return an error if a character set or national character set other than those in the preceding lists is used as the client or database character set. With Instant Client Light, the error messages are only obtained in English. Therefore, in setting `NLS_LANG`, the valid values are:

    American_territory.charset

where territory can be any valid territory that can be specified with `NLS_LANG` and charset is one of the character sets listed above.

Instant Client Light can also operate with the OCI Environment handles created in the `OCI_UTF16` mode.

See Also: Oracle Database Globalization Support Guide for more information about NLS settings

#### Operation of Instant Client Light

OCI applications, by default, look for the OCI Data Shared Library, `libociei.so` (or `Oraociei10.dll` on Windows) on the `LD_LIBRARY_PATH` (`PATH` on Windows) to determine if the application should operate in the Instant Client mode. If this library is not found, then OCI tries to load the Instant Client Light Data Shared Library, `libociicus.so` (or `Oraociicus10.dll` on Windows). If the Instant Client Light library is found, then the application operates in the Instant Client Light mode. Otherwise, a full `ORACLE_HOME` based installation is assumed

#### Installation of Instant Client Light

Instant Client Light can be installed in one of the following ways:

1. From OTN.

   Go to the Instant Client URL:

       http://www.oracle.com/technology/software/tech/oci/instantclient/

   For Instant Client Light, instead of downloading and expanding the `basic.zip` package, download and unzip the `basiclite.zip` package. The `instantclient_10_2` directory in which the Instant Client Light libraries are unzipped should be empty before the unzip.

2. From Client Admin Install.

   Instead of copying `libociei.so` (or `Oraociei10.dll` on Windows) from the `ORACLE_HOME/instantclient` directory, copy `libociicus.so` (or `Oraociic10.dll` on Windows) from the `ORACLE_HOME/instantclient/light` subdirectory. That is, the Instant Client directory on the `LD_LIBRARY_PATH` (`PATH` on Windows) should contain the Instant Client Light Data Shared Library, `libociicus.so` (`Oraociicus10.dll` on Windows), instead of the larger OCI Instant Client Data Shared Library, `libociei.so` (`Oraociei10.dll` on Windows).

3. From Oracle Universal Installer.

   If the Instant Client option is selected from the Oracle Universal Installer (OUI), then `libociei.so` (or `Oraociei10.dll` on Windows) is installed in the base directory of the installation which is going to be placed on the `LD_LIBRARY_PATH` (`PATH` on Widows). This is so that the Instant Client Light is not enabled by default. The Instant Light Client Data Shared Library, `libociicus.so` (or `Oraociicus10.dll` on Windows), is installed in the `light` subdirectory of the base directory. Therefore, to operate in the Instant Client Light mode, the OCI Data Shared Library, `libociei.so` (or `Oraociei10.dll` on Windows) must be deleted or renamed and the Instant Client Light library must be copied up from the `light` subdirectory to the base directory of the installation.

   For example, if the OUI has installed the Instant Client in `my_oraic_10_2` directory on the `LD_LIBRARY_PATH` (`PATH` on Windows), then you need to do the following to operate in the Instant Client Light mode:

       cd my_oraic_10_2
       rm libociei.so
       mv light/libociicus.so .

   Note: All the Instant Client files should always be copied and installed in an empty directory. This is to make sure that no incompatible binaries exist in the installation.

### SDK for Instant Client

The SDK can be downloaded from the Instant Client web page:

    http://www.oracle.com/technology/tech/oci/instantclient/instantclient.html

- The Instant Client SDK package has both C and C++ header files and a Makefile for developing OCI and OCCI applications while in an Instant Client environment. Developed applications can be deployed in any client environment.

- The SDK contains C and C++ demonstration programs.

- On Windows, libraries required to link the OCI or OCCI applications are also included. `Make.bat` is provided to build the demos.

- On Unix or Linux, the Makefile `demo.mk` is provided to build the demos. The `instantclient_10_2` directory must be on the `LD_LIBRARY_PATH` before linking the application. The OCI and OCCI programs require the presence of `libclntsh.so` and `libocci.sh` symbolic links in the `instantclient_10_2` directory. `demo.mk` creates these before the link step. These symbolic links can also be created in a shell:

      cd instantclient_10_2
      ln -s libclntsh.so.10.1 libclntsh.so
      ln -s libocci.so.10.1 libocci.so

- The SDK also contains the Object Type Translator (OTT) utility and its classes to generate the application header files.